<?php
//手动接收客户端获取采购单数据，非认真人士切勿修改，特此封印
//作者:gqc,请联系他，并提前支付一块服务费，谢谢
require_once(Kohana::find_file('vendor','phprpc/phprpc_client'));
class Controller_Handclient extends Controller
{
    public static $commonTbls = array('purchaseorders', 'purchaseorder_details', 'purchaseorder_details_sites');
    public $serverUrl = array(
        'test' => 'http://192.168.5.102:8110/handserver',
        'product' => 'http://192.168.5.6:703/handserver'
    );

    /**
     * 获取采购单、采购明细、明细站点数据
     */
    public function action_get_newerp_pu_info () {
        if(!isset($_GET['env']) || !in_array($_GET['env'], array('test', 'product'))) {
            echo 'no env params.';
            exit;
        }
        isset($_GET['puIds']) && $puIds = str_replace('，', ',', trim($_GET['puIds']));
        if(!isset($puIds) || empty($puIds)) {
            echo 'Wrong puIds,please check it.';
            exit;
        }
        $puIds = explode(',', $puIds);
        $client = new PHPRPC_Client($this->serverUrl[$_GET['env']]);
        $puInfos = $client->getNewErpPuInfo($puIds, 'fbawarehousetable');
        if(isset($puInfos['state']) && $puInfos['state'] != 200) {
            echo isset($puInfos['info']) ? $puInfos['info'] : 'Server something wrong';
            exit;
        }

        foreach (self::$commonTbls as $tbl) {
            $tbl = 'wms_' . $tbl;
            if(empty($puInfos[$tbl])) {
                echo 'The table of newerp =>' . $tbl . ' have nothing data';
                continue;
            }

            foreach ($puInfos[$tbl] as $tblData) {
                $tblData['crontrigger'] = 1;
                isset($tblData['sku_name']) && $tblData['sku_name'] = addslashes($tblData['sku_name']);
                $sql = 'INSERT INTO ' . $tbl . ' (' . join(',', array_keys($tblData)) . ') VALUES (';
                $sql .= '\'' . join('\',\'', $tblData) . '\'';
                $sql .= ')ON DUPLICATE KEY UPDATE ';
                $updateStr = '';
                foreach ($tblData as $col=>$colVal) {
                    $updateStr .= '`' . $col . '` = \'' . $colVal . '\',';
                }
                $sql .= rtrim($updateStr, ',');
                DB::query(Database::INSERT, $sql)->execute('newerp');

                if($tbl == 'wms_purchaseorder_details') {
                    if($tblData['status'] == 0 && $tblData['recieve_quantity'] == 0
                        && $tblData['check_quantity'] == 0 && $tblData['ware_quantity'] == 0) {
                        $sql = 'UPDATE `wms_purchaseorder_details` SET `status` = 20 WHERE `id` = ' . $tblData['id'];
                        DB::query(Database::UPDATE, $sql)->execute('newerp');
                    }
                }
            }
        }

        echo "<span style='color: darkgreen'><h3>DO SUCCESS!</h3></span>" .
            "<a href='http://fbawarehouse.youkeshu.com/index.php/Warehouse/Purchaseorders/index.html'><h1>BACK</h1></a>'";
        exit;
    }

    //回写采购单失败
    public function action_pushWareData() {
        if(!isset($_GET['env']) || !in_array($_GET['env'], array('test', 'product'))) {
            echo 'no env params.';
            exit;
        }
        isset($_GET['puIds']) && $puIds = str_replace('，', ',', trim($_GET['puIds']));
        if(!isset($puIds) || empty($puIds)) {
            echo 'Wrong puIds,please check it.';
            exit;
        }

        //判断采购单是否有效
        $puData = DB::select_array(array('id', 'status'))
            ->from('wms_purchaseorders')
            ->where('id', 'IN', explode(',', $_GET['puIds']))
            ->execute('newerp')
            ->as_array('id', 'status');
        if(empty($puData))die('Error or invalid param puIds');

        //获取采购单明细数据
        $puDetailData = DB::select_array(array('id','purchaseorder_id','sku','quantity','recieve_quantity','check_quantity','return_quantity','ware_quantity','status'))
            ->from('wms_purchaseorder_details')
            ->where('purchaseorder_id', 'IN', explode(',', $_GET['puIds']))
            ->execute('newerp')
            ->as_array();

        //获取采购单明细站点数据
        $puDetailSiteData = DB::select_array(array('id','purchaseorders_id','sku','site_id'))
            ->from('wms_purchaseorder_details_sites')
            ->where('purchaseorders_id', 'IN', explode(',', $_GET['puIds']))
            ->execute('newerp')
            ->as_array();

        //读取入库数据
        $wareData = array();
        $temp = DB::select_array(array(
            DB::expr("SUM(`warehouse_quantity`) AS `sumPuSkuSiteWareQty`"),'purchaseorders_id', 'site_id', 'sku'))
            ->from('wms_warehouseorders')
            ->where('purchaseorders_id', 'IN', explode(',', $_GET['puIds']))
            ->and_where('type', '=', 10)
            ->and_where('site_id', '>', 0)
            ->group_by('purchaseorders_id', 'sku', 'site_id')
            ->execute('newerp')
            ->as_array();
        foreach ($temp as $_temp) {
            $puId = $_temp['purchaseorders_id'];
            $sku = $_temp['sku'];
            $siteId = $_temp['site_id'];
            $wareData[$puId][$sku]['siteQty'][$siteId]['wareQty'] = $_temp['sumPuSkuSiteWareQty'];
            isset($wareData[$puId][$sku]['sumWareQty']) ?
                $wareData[$puId][$sku]['sumWareQty'] += $_temp['sumPuSkuSiteWareQty'] :
                $wareData[$puId][$sku]['sumWareQty'] = $_temp['sumPuSkuSiteWareQty'];
        }
        //读取质检数据
        $checkData = array();
        $temp = DB::select_array(array(
            DB::expr("SUM(`check_quantity`) AS `checkQty`"),
            DB::expr("SUM(`unquality_count`) AS `badQty`"),
            'purchaseorder_id', 'sku', 'site_id'))
            ->from('wms_check_quality_details')
            ->where('purchaseorder_id', 'IN', explode(',', $_GET['puIds']))
            ->group_by('purchaseorder_id', 'sku', 'site_id')
            ->execute('newerp')
            ->as_array();
        foreach ($temp as $_temp) {
            $puId = $_temp['purchaseorder_id'];
            $sku = $_temp['sku'];
            $siteId = $_temp['site_id'];
            $checkData[$puId][$sku]['siteQty'][$siteId]['checkQty'] = $_temp['checkQty'];
            $checkData[$puId][$sku]['siteQty'][$siteId]['badQty'] = $_temp['badQty'];
            isset($checkData[$puId][$sku]['sumCheckQty']) ?
                $checkData[$puId][$sku]['sumCheckQty'] += $_temp['checkQty'] :
                $checkData[$puId][$sku]['sumCheckQty'] = $_temp['checkQty'];
            isset($checkData[$puId][$sku]['sumBadQty']) ?
                $checkData[$puId][$sku]['sumBadQty'] += $_temp['badQty'] :
                $checkData[$puId][$sku]['sumBadQty'] = $_temp['badQty'];
        }
        //读取收货数据
        $recData = array();
        $temp = DB::select_array(array(
            DB::expr("SUM(d.`arrival_quantity`) AS `recQty`"),
            'i.purchaseorder_id', 'd.sku', 'd.site_id'))
            ->from(array('wms_recieve_details', 'd'))
            ->join(array('wms_recieve_invoices', 'i'), 'LEFT')
            ->on('d.recieve_invoice_id', '=', 'i.id')
            ->where('i.purchaseorder_id', 'IN', explode(',', $_GET['puIds']))
            ->group_by('i.purchaseorder_id', 'd.sku', 'i.site_id')
            ->execute('newerp')
            ->as_array();

        foreach ($temp as $_temp) {
            $puId = $_temp['purchaseorder_id'];
            $sku = $_temp['sku'];
            $siteId = $_temp['site_id'];
            $recData[$puId][$sku]['siteQty'][$siteId]['recQty'] = $_temp['recQty'];
            isset($recData[$puId][$sku]['sumRecQty']) ?
                $recData[$puId][$sku]['sumRecQty'] += $_temp['recQty'] :
                $recData[$puId][$sku]['sumRecQty'] = $_temp['recQty'];
        }

        //更新明细表数据\状态
        foreach ($puDetailData as $_detail) {
            $puId = $_detail['purchaseorder_id'];
            $sku = $_detail['sku'];
            $detailModify = array();
            $detailModify['recieve_quantity'] = isset($recData[$puId][$sku]['sumRecQty']) ? $recData[$puId][$sku]['sumRecQty'] : 0;
            $detailModify['check_quantity'] = isset($checkData[$puId][$sku]['sumCheckQty']) ? $checkData[$puId][$sku]['sumCheckQty'] : 0;
            $detailModify['return_quantity'] = isset($checkData[$puId][$sku]['sumBadQty']) ? $checkData[$puId][$sku]['sumBadQty'] : 0;
            $detailModify['ware_quantity'] = isset($wareData[$puId][$sku]['sumWareQty']) ? $wareData[$puId][$sku]['sumWareQty'] : 0;

            if($detailModify['ware_quantity'] >= $_detail['quantity'])
                $detailModify['status'] = 70;

            DB::update('wms_purchaseorder_details')
                ->set($detailModify)
                ->where('id', '=', $_detail['id'])
                ->execute('newerp');
        }
        //更新SITE表
        foreach ($puDetailSiteData as $_siteDetail) {
            $puId = $_siteDetail['purchaseorders_id'];
            $sku = $_siteDetail['sku'];
            $siteId = $_siteDetail['site_id'];
            $detailModify = array();
            $detailModify['recieve_quantity'] = isset($recData[$puId][$sku]['siteQty'][$siteId]['recQty']) ? $recData[$puId][$sku]['siteQty'][$siteId]['recQty'] : 0;
            $detailModify['check_quantity'] = isset($checkData[$puId][$sku]['siteQty'][$siteId]['checkQty']) ? $checkData[$puId][$sku]['siteQty'][$siteId]['checkQty'] : 0;
            $detailModify['return_quantity'] = isset($checkData[$puId][$sku]['siteQty'][$siteId]['badQty']) ? $checkData[$puId][$sku]['siteQty'][$siteId]['badQty'] : 0;
            $detailModify['ware_quantity'] = isset($wareData[$puId][$sku]['siteQty'][$siteId]['wareQty']) ? $wareData[$puId][$sku]['siteQty'][$siteId]['wareQty'] : 0;

            DB::update('wms_purchaseorder_details_sites')
                ->set($detailModify)
                ->where('id', '=', $_siteDetail['id'])
                ->execute('newerp');
        }
        //更新采购单明细表状态
        //同步
        echo "<span style='color: darkgreen'><h3>DO SUCCESS!</h3></span>" .
            "<a href='http://fbawarehouse.youkeshu.com/index.php/Warehouse/Purchaseorders/index.html'><h1>BACK</h1></a>'";
    }

    //删除质检重复项
    public function action_deletePardonCheck() {
        if(empty($_GET['recieveDetailsId']))die('recieveDetailsId ERROR!');

        $pardonCheck = DB::select_array(array('id','purchaseorder_id','recieve_detail_id','sku','check_quantity','stockin_num'))
            ->from('wms_check_quality_details')
            ->where('recieve_detail_id', '=', $_GET['recieveDetailsId'])
            ->execute('newerp')
            ->as_array();

        $checkAllNum = count($pardonCheck);
        if($checkAllNum <= 1) {
            echo "<span style='color: darkgreen'><h3>NOTHING DELETE!</h3></span>" .
                "<a href='http://fbawarehouse.youkeshu.com/index.php/Warehouse/CheckOrders/scan_check_index.html'><h1>BACK</h1></a>'";
        } else {
            $checkIds = array();
            foreach ($pardonCheck as $pc) {
                if($pc['stockin_num'] > 0)continue;
                $checkIds[] = $pc['id'];
            }
            if(empty($checkIds)) {
                echo 'All of the check order have been stock in warehouse, please contact IT';
                exit;
            }
            if(count($checkIds) == $checkAllNum)unset($checkIds[0]);
            DB::delete('wms_check_quality_details')->where('id', 'IN', $checkIds)->execute('newerp');
            echo "<span style='color: darkgreen'><h3>DO SUCCESS!</h3></span>" .
                "<a href='http://fbawarehouse.youkeshu.com/index.php/Warehouse/CheckOrders/scan_check_index.html'><h1>BACK</h1></a>'";
        }
        exit;
    }

    //Shipment没有中文名称
    public function action_updateShipmentSkuCnName() {
        if(empty($_GET['shipmentid']))die('shipmentid ERROR!');
        $temp = DB::select_array(array(array('d.id', 'detailId'), 'd.sku'))
            ->from(array('fba_inbound_shipment_plan_detail', 'd'))
            ->join(array('fba_inbound_shipment_plan', 'i'), 'LEFT')
            ->on('d.inbound_shipment_plan_id', '=', 'i.id')
            ->where('i.shipmentid', '=', $_GET['shipmentid'])
            ->execute('newerp')
            ->as_array('detailId', 'sku');

        if(empty($temp))die('NOTHING TO UPDATE!');
        $actualSkuArr = array_unique($temp);

        $skuNames = DB::select_array(array('d.sku','i.name'))
            ->from(array('skusystem_sku_cnname', 'd'))
            ->join(array('skusystem_cnname', 'i'), 'LEFT')
            ->on('d.attr_id', '=', 'i.id')
            ->where('d.sku', 'IN', $actualSkuArr)
            ->execute('newerp')
            ->as_array('sku','name');

        if(count($skuNames) != count($actualSkuArr)) {
            $diffSku = array_diff($actualSkuArr, array_keys($skuNames));
            $error = 'Data common may error,please send ';
            $error .= join(',', $diffSku);
            $error .= " to Zeng Chunwen for check:<br/>";
            $error .= 'fbawarehousedbm.youkeshu.com/phpmyadmin.skusystem_cnname、skusystem_sku_cnname common error';
            echo $error;exit;
        }

        foreach ($temp as $detailId=>$sku) {
            $upData = array();
            $upData['sku_name'] = isset($skuNames[$sku]) ? addslashes($skuNames[$sku]) : '';
            DB::update('fba_inbound_shipment_plan_detail')->set($upData)->where('id', '=', $detailId)->execute('newerp');
        }
        echo "<span style='color: darkgreen'><h3>DO SUCCESS!</h3></span>" .
            "<a href='http://fbawarehouse.youkeshu.com/index.php/Inbound/Inboundshipmentplan/index?shipmentid={$_GET['shipmentid']}'><h1>BACK</h1></a>'";
        exit;
    }

    //更新单个采购单一条线中文名称
    public function action_updatePuSkuName() {
        if(empty($_GET['puId']))die('puId Error');
        $puId = intval(trim($_GET['puId']));

        $detailSku = DB::select_array(array('id','sku'))
            ->from('wms_purchaseorder_details')
            ->where('purchaseorder_id', '=', $puId)
            ->execute('newerp')
            ->as_array('id', 'sku');

        if(empty($detailSku)) die('empty data!');
        $skuNames = DB::select_array(array('d.sku','i.name'))
            ->from(array('skusystem_sku_cnname', 'd'))
            ->join(array('skusystem_cnname', 'i'), 'LEFT')
            ->on('d.attr_id', '=', 'i.id')
            ->where('d.sku', 'IN', $detailSku)
            ->execute('newerp')
            ->as_array('sku','name');

        if(count($skuNames) != count($detailSku)) {
            $diffSku = array_diff($detailSku, array_keys($skuNames));
            $error = 'Data common may error,please send ';
            $error .= join(',', $diffSku);
            $error .= " to Zeng Chunwen for check:<br/>";
            $error .= 'fbawarehousedbm.youkeshu.com/phpmyadmin.skusystem_cnname、skusystem_sku_cnname common error'. "<br/>";
            $error .= DB::select_array(array('d.sku','i.name'))
                ->from(array('skusystem_sku_cnname', 'd'))
                ->join(array('skusystem_cnname', 'i'), 'LEFT')
                ->on('d.attr_id', '=', 'i.id')
                ->where('d.sku', 'IN', $detailSku)
                ->compile('newerp');
            echo $error;exit;
        }

        foreach ($skuNames as $sku=>$skuName) {
            $upData = array();
            $upData['sku_name'] = addslashes($skuName);
            DB::update('wms_purchaseorder_details')
                ->set($upData)
                ->where('purchaseorder_id', '=', $puId)
                ->and_where('sku', '=', $sku)
                ->execute('newerp');

            $sql = 'UPDATE `wms_recieve_details` AS d
            LEFT JOIN wms_recieve_invoices AS r
                    ON r.id = d.`recieve_invoice_id`
            SET d.`sku_name` = \'' . $upData['sku_name'] . '\'
            WHERE r.purchaseorder_id = \'' . $puId . '\' AND d.sku = \'' . $sku . '\'';
            DB::query(Database::UPDATE, $sql)->execute('newerp');

            DB::update('wms_check_quality_details')
                ->set($upData)
                ->where('purchaseorder_id', '=', $puId)
                ->and_where('sku', '=', $sku)
                ->execute('newerp');

            DB::update('wms_warehouseorders')
                ->set($upData)
                ->where('purchaseorders_id', '=', $puId)
                ->and_where('sku', '=', $sku)
                ->execute('newerp');
        }

        echo "<span style='color: darkgreen'><h3>DO SUCCESS!</h3></span>" . "<a href='http://fbawarehouse.youkeshu.com/index.php/Warehouse/Purchaseorders/index.html'><h1>BACK</h1></a>'";
        exit;
    }
}